package com.shibo.generator.service;

import com.shibo.generator.entity.TableColumns;
import com.shibo.generator.propConfig.DatasourceConfig;
import com.shibo.generator.entity.TableInfo;
import com.shibo.generator.util.DataBaseUtils;
import com.shibo.generator.util.DataTypeEnums;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Optional;

/*
 * information_schema数据字典中的表信息
 * */
@Service
public class TableInfoService {

    private Logger logger = LoggerFactory.getLogger(this.getClass());

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Autowired
    private DatasourceConfig datasourceSettings;

    /*
     * 根据表名获取表的列
     * */
    public List<TableInfo> getTableColumns(String tableName) {
        StringBuilder sql = new StringBuilder();
        sql.append(" SELECT TABLE_SCHEMA AS tableSchema,TABLE_NAME AS tableName,COLUMN_NAME AS columnName,");
        sql.append(" ORDINAL_POSITION AS ordinalPosition,IS_NULLABLE AS notNullFlag,DATA_TYPE AS dataType,");
        sql.append(" (ifnull(CHARACTER_MAXIMUM_LENGTH,NUMERIC_PRECISION)) AS columnLength,COLUMN_KEY AS  columnKey,COLUMN_COMMENT AS columnComment,COLUMN_DEFAULT AS columnDefault");
        sql.append(" FROM information_schema.columns WHERE 1=1 AND table_schema = '");
        sql.append(DataBaseUtils.getDatabaseName(datasourceSettings.getUrl()));
        sql.append("' AND table_name = '");
        sql.append(tableName);
        sql.append("'");
        logger.info("jdbc getListByTable sql:" + sql);
        return jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(TableInfo.class));
    }

    /*
    * 转化成前端可操作的数据
    * */
    public List<TableColumns> tableColumns(String tableName) {
        List<TableColumns> list = new ArrayList<>();
        for (TableInfo tableColumn : getTableColumns(tableName)) {
            list.add(TableColumns.builder()
                    .name(tableColumn.getColumnName())
                    .type(tableColumn.getDataType())
                    .defaultVal(tableColumn.getColumnDefault())
                    .desc(tableColumn.getColumnComment())
                    .primary(tableColumn.getColumnKey().contains("PRI"))
                    .notNull(tableColumn.getNotNullFlag().contains("YES"))
                    .len(tableColumn.getColumnLength() != null ? Integer.parseInt(tableColumn.getColumnLength()) : 0)
                    .build());
        }
        return list;
    }

    /*
     * 获取schema中所有表
     * */
    public List<Map<String, Object>> getTables() {
        return jdbcTemplate.queryForList("select TABLE_NAME  AS tableName,TABLE_COMMENT AS comment,CREATE_TIME as createTime from information_schema.`TABLES` where TABLE_SCHEMA = '"
                + DataBaseUtils.getDatabaseName(datasourceSettings.getUrl()) + "' " +
                "and  TABLE_TYPE = 'BASE TABLE'");
    }

    /*
     * 根据表获取所有的列
     * */

    /*
     * 获取表信息
     * */
    public boolean tableExist(String tableName) {
        return Optional.ofNullable(jdbcTemplate.queryForObject("SELECT count(*) FROM information_schema.`TABLES` where TABLE_NAME = '" + tableName + "'", Integer.class)).orElse(0) > 0;
    }

    /*
     * 执行sql
     * */
    public void execSql(String sql) {
        if (sql != null && sql.length() > 0)
            jdbcTemplate.execute(sql);
    }
}
